package com.dai.uvs.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.dai.uvs.data.Equipment;
import com.dai.uvs.data.Land;
import com.dai.uvs.data.Users;

public class DataManager {
	private String dbURL = "";
	private String dbUserName = "";
	private String dbPassword = "";
	private String deutsch = "";
	private String equipmentCd;
	private String transText;
	private Land land = new Land();
	private Equipment equipment = new Equipment();

	public String getTransText() {
		return transText;
	}

	public void setTransText(String transText) {
		this.transText = new String(transText);
	}

	public String getEquipmentCd() {
		return equipmentCd;
	}

	public void setEquipmentCd(String equipmentCd) {
		this.equipmentCd = new String(equipmentCd);
	}

	public void setDbURL(String dbURL) {
		this.dbURL = dbURL;
	}

	public String getDbURL() {
		return dbURL;
	}

	public void setDbUserName(String dbUserName) {
		this.dbUserName = dbUserName;
	}

	public String getDbUserName() {
		return dbUserName;
	}

	public void setDbPassword(String dbPassword) {
		this.dbPassword = dbPassword;
	}

	public String getDbPassword() {
		return dbPassword;
	}

	public void setDeutsch(String deutsch) {
		this.deutsch = deutsch;
	}

	public String getDeutsch() {
		return deutsch;
	}

	public void setLang(String lang) {
		new String(lang);
	}

	/*
	 * Open database connection
	 */
	public Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(getDbURL(), getDbUserName(),
					getDbPassword());
		} catch (SQLException e) {
			System.out.println("Could not connect to DB");
			e.printStackTrace();
		}
		return conn;
	}

	/*
	 * Get User Data
	 */
	public Users getUserData(String username, String password) {
		Users user = new Users();
		Connection conn = getConnection();

		if (conn != null) {
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn
						.prepareStatement("SELECT * FROM D$TV0B.USERS WHERE USERS_EMAIL = ? AND USERS_PASSWORD = ?");
				ps.setString(1, username);
				ps.setString(2, password);
				rs = ps.executeQuery();
				while (rs.next()) {
					user.setEmail(rs.getString("USERS_EMAIL"));
					user.setPassword(rs.getString("USERS_PASSWORD"));

				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					ps.close();
				} catch (Exception e) {
					e.printStackTrace();
					putConnection(conn);
				}

			}// end finally
		}
		return user;

	}

	/*
	 * 
	 * 
	 */
	public Users getUserLand(String username) {
		Users user = new Users();
		user.setLand(land);

		Connection conn = getConnection();

		if (conn != null) {
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn
						.prepareStatement("SELECT a.USERS_EMAIL,b.LAND_TEXT,b.LAND_LANG_CD FROM D$TV0B.USERS a, D$TV0B.LAND b WHERE a.USERS_EMAIL = ? AND a.USERS_COUNTRY_CD = b.LAND_CODE");
				ps.setString(1, username);
				rs = ps.executeQuery();
				while (rs.next()) {
					user.setEmail(rs.getString("USERS_EMAIL"));
					user.getLand().setLandText(rs.getString("LAND_TEXT"));
					user.getLand().setLandLangCd(rs.getString("LAND_LANG_CD"));

				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					ps.close();
				} catch (Exception e) {
					e.printStackTrace();
					putConnection(conn);
				}

			}// end finally
		}
		return user;

	}

	public ArrayList<Land> getComboBoxValues() {
		ArrayList<Land> countryList = new ArrayList<Land>();
		Connection conn = getConnection();
		if (conn != null) {
			ResultSet rs = null;
			Statement stm = null;
			try {
				String strQuery = ("SELECT * FROM D$TV0B.LAND");
				stm = conn.createStatement();
				rs = stm.executeQuery(strQuery);
				while (rs.next()) {
					Land country = new Land();
					country.setLandLangCd(rs.getString("LAND_LANG_CD"));
					country.setLandText(rs.getString("LAND_TEXT"));
					countryList.add(country);

				}
			}// end of try
			catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					stm.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				putConnection(conn);
			}// end finally
		}// end if
		return countryList;
	}

	/*
	 * 
	 */
	public ArrayList<Equipment> getComboBoxValuesEQ() {
		ArrayList<Equipment> equipmentList = new ArrayList<Equipment>();
		Connection conn = getConnection();
		if (conn != null) {
			ResultSet rs = null;
			Statement stm = null;
			try {
				String strQuery = ("SELECT DISTINCT(DEQT_TYP_EQUIPM_CD) FROM D$TV0B.V0BRDEQT");
				stm = conn.createStatement();
				rs = stm.executeQuery(strQuery);
				while (rs.next()) {
					Equipment equipment = new Equipment();
					equipment.setDEQT_TYP_EQUIPM_CD(rs
							.getString("DEQT_TYP_EQUIPM_CD"));
					equipmentList.add(equipment);

				}
			}// end of try
			catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					stm.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				putConnection(conn);
			}// end finally
		}// end if
		return equipmentList;
	}

	/*
	 * 
	 */
	public ArrayList<Equipment> getGesamt(String username) {
		ArrayList<Equipment> equipmentList = new ArrayList<Equipment>();
		Connection conn = getConnection();
		String result = getLandCd(username);
		if (conn != null) {
			ResultSet rs = null;
			Statement stm = null;
			try {
				String strQuery = ("SELECT * FROM D$TV0B.V0BRDEQT a WHERE a.DEQT_LANGUAGE_CD ='de' AND a.DEQT_MANUF_GROU_CD = '0M' AND a.DEQT_EQUIPMENT_CD NOT IN (SELECT b.DEQT_EQUIPMENT_CD FROM D$TV0B.V0BRDEQT b WHERE b.DEQT_MANUF_GROU_CD = '0M' AND b.DEQT_LANGUAGE_CD ='"
						+ result + "') LIMIT 15");
				System.out.println("SQL:: " + strQuery);
				stm = conn.createStatement();
				rs = stm.executeQuery(strQuery);
				while (rs.next()) {
					Equipment equipment = new Equipment();
					equipment.setDEQT_TYP_EQUIPM_CD(rs
							.getString("DEQT_TYP_EQUIPM_CD"));
					equipment.setDEQT_MANUF_GROU_CD(rs
							.getString("DEQT_MANUF_GROU_CD"));
					equipment.setDEQT_MANUF_GROU_CD(rs
							.getString("DEQT_MODEL_DESIGNA"));
					equipment.setDEQT_EQUIPMENT_CD(rs
							.getString("DEQT_EQUIPMENT_CD"));
					equipment.setDEQT_LANGUAGE_CD(rs
							.getString("DEQT_LANGUAGE_CD"));
					equipment.setDEQT_TEXT(rs.getString("DEQT_TEXT"));
					equipmentList.add(equipment);

				}
			}// end of try
			catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					stm.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				putConnection(conn);
			}// end finally
		}// end if
		return equipmentList;
	}

	/*
	 * 
	 */
	public String getLandCd(String username) {
		Connection conn = getConnection();
		if (conn != null) {
			ResultSet rs = null;
			Statement stm = null;
			try {
				String strQuery = ("SELECT LAND_LANG_CD FROM D$TV0B.USERS a, D$TV0B.LAND b WHERE a.USERS_EMAIL ='"
						+ username + "' AND a.USERS_COUNTRY_CD = b.LAND_CODE");
				stm = conn.createStatement();
				rs = stm.executeQuery(strQuery);
				System.out.println("SQL:::" + strQuery);
				while (rs.next()) {
					land.setLandLangCd(rs.getString("LAND_LANG_CD"));
				}
			}// end of try
			catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					stm.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				putConnection(conn);
			}// end finally
		}// end if
		return land.getLandLangCd();
	}

	/*
	 * 
	 * 
	 */

	public ArrayList<Equipment> getGesamtEq(String str) {
		equipment.setDEQT_TYP_EQUIPM_CD(str);
		ArrayList<Equipment> equipmentList = new ArrayList<Equipment>();
		Connection conn = getConnection();
		if (conn != null) {
			ResultSet rs = null;
			Statement stm = null;
			try {
				String strQuery = ("SELECT * FROM D$TV0B.V0BRDEQT a WHERE a.DEQT_MANUF_GROU_CD = '0M' AND a.DEQT_TYP_EQUIPM_CD = '"
						+ equipment.getDEQT_TYP_EQUIPM_CD()
						+ "' AND a.DEQT_LANGUAGE_CD ='de' AND a.DEQT_EQUIPMENT_CD NOT IN (SELECT b.DEQT_EQUIPMENT_CD FROM D$TV0B.V0BRDEQT b WHERE  b.DEQT_MANUF_GROU_CD = '0M' AND b.DEQT_LANGUAGE_CD ='"
						+ land.getLandLangCd() + "')");
				stm = conn.createStatement();
				rs = stm.executeQuery(strQuery);
				while (rs.next()) {
					Equipment equipment = new Equipment();
					equipment.setDEQT_TYP_EQUIPM_CD(rs
							.getString("DEQT_TYP_EQUIPM_CD"));
					equipment.setDEQT_MANUF_GROU_CD(rs
							.getString("DEQT_MANUF_GROU_CD"));
					equipment.setDEQT_MANUF_GROU_CD(rs
							.getString("DEQT_MODEL_DESIGNA"));
					equipment.setDEQT_EQUIPMENT_CD(rs
							.getString("DEQT_EQUIPMENT_CD"));
					equipment.setDEQT_LANGUAGE_CD(rs
							.getString("DEQT_LANGUAGE_CD"));
					equipment.setDEQT_TEXT(rs.getString("DEQT_TEXT"));
					equipmentList.add(equipment);

				}
			}// end of try
			catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					stm.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				putConnection(conn);
			}// end finally
		}// end if
		return equipmentList;
	}

	/*
	 * 
	 */
	public ArrayList<Equipment> getTrans(String eq, String eqCd) {
		equipment.setDEQT_TYP_EQUIPM_CD(eq);
		setEquipmentCd(eqCd);
		ArrayList<Equipment> equipmentList = new ArrayList<Equipment>();
		Connection conn = getConnection();
		if (conn != null) {
			ResultSet rs = null;
			Statement stm = null;
			try {
				String strQuery = ("SELECT * FROM D$TV0B.V0BRDEQT WHERE DEQT_TYP_EQUIPM_CD='"
						+ equipment.getDEQT_TYP_EQUIPM_CD()
						+ "' AND DEQT_LANGUAGE_CD IN ('de','en') AND DEQT_MANUF_GROU_CD = '0M' AND DEQT_EQUIPMENT_CD = '"
						+ equipment.getDEQT_EQUIPMENT_CD() + "'");
				stm = conn.createStatement();
				rs = stm.executeQuery(strQuery);
				while (rs.next()) {
					Equipment equipment = new Equipment();
					equipment.setDEQT_TYP_EQUIPM_CD(rs
							.getString("DEQT_TYP_EQUIPM_CD"));
					equipment.setDEQT_MANUF_GROU_CD(rs
							.getString("DEQT_MANUF_GROU_CD"));
					equipment.setDEQT_MANUF_GROU_CD(rs
							.getString("DEQT_MODEL_DESIGNA"));
					equipment.setDEQT_EQUIPMENT_CD(rs
							.getString("DEQT_EQUIPMENT_CD"));
					equipment.setDEQT_LANGUAGE_CD(rs
							.getString("DEQT_LANGUAGE_CD"));
					equipment.setDEQT_TEXT(rs.getString("DEQT_TEXT"));
					equipmentList.add(equipment);

				}
			}// end of try
			catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					stm.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				putConnection(conn);
			}// end finally
		}// end if
		return equipmentList;
	}

	/*
	 * 
	 */
	public ArrayList<String> getText(String text) {
		equipment.setDEQT_TEXT(text);
		ArrayList<String> equipmentList = new ArrayList<String>();
		Connection conn = getConnection();
		if (conn != null) {
			ResultSet rs = null;
			Statement stm = null;
			try {
				String strQuery = ("SELECT * FROM D$TV0B.V0BRDEQT WHERE DEQT_LANGUAGE_CD ='"
						+ land.getLandLangCd()
						+ "' AND LOWER(DEQT_TEXT) like '"
						+ getTransText() + "%'");
				stm = conn.createStatement();
				rs = stm.executeQuery(strQuery);
				while (rs.next()) {
					if (rs.getString("DEQT_TEXT").startsWith(
							getTransText().toLowerCase()))
						equipmentList.add(rs.getString("DEQT_TEXT"));
				}
			}// end of try
			catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					stm.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				putConnection(conn);
			}// end finally
		}// end if
		return equipmentList;
	}

	/*
	 * Close open database connection
	 */
	public void putConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				System.out.println("Enable to close DB connection");
				e.printStackTrace();
			}
		}
	}
}
